﻿GO
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ARITHABORT ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ONuse p301
go

ALTER VIEW dbo.vKpo
AS
SELECT     K_KPO, K_UKS, NPP_KPO, K_OBRASCH, F_DEN, F_KRIZIS, D_OBRASCH, V_OBRASCH, K_INF_CENTR, F_KLIENT, F_KONS, K_SPEC, K_HELP_ORG, 
                      N_HELP, K_SP, F_DETI, K_UP, K_OBR, K_SOCSTAT, K_RAB, K_PRICH, K_KONS, N_FA, N_IM, N_OT, K_POL, K_POM, N_PRIM, GOD_R, K_ADR, 
                      N_PRICH, ISNULL(D_KONS, CASE WHEN F_KONS = 1 THEN D_OBRASCH END) AS D_KONS, ISNULL(V_KONS, 
                      CASE WHEN F_KONS = 1 THEN V_OBRASCH END) AS V_KONS, LTRIM(STR(NPP_KPO)) + ' - ' + LTRIM(STR(YEAR(D_OBRASCH))) AS NPP_KPOyear, 
                      dbo.N_FIO(N_FA, N_IM, N_OT) AS N_FIO
FROM         dbo.b_kpo

GO
/*The column:  C_LET is being dropped from table:  [dbo].[b_klient].*/
IF EXISTS (select top 1 1 from [dbo].[b_klient])
	RAISERROR ('Rows detected, failing schema update due to possible data loss.', 16, 127) WITH NOWAIT
GO
PRINT N'Dropping extended properties'
GO
EXEC sp_dropextendedproperty N'AllowZeroLength', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'Attributes', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'CollatingOrder', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'ColumnHidden', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'ColumnOrder', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'ColumnWidth', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'DataUpdatable', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'DefaultValue', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'MS_DecimalPlaces', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'MS_DisplayControl', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'Name', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'OrdinalPosition', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'Required', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'Size', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'SourceField', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'SourceTable', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
EXEC sp_dropextendedproperty N'Type', 'SCHEMA', N'dbo', 'TABLE', N'b_klient', 'COLUMN', N'C_LET'
GO
PRINT N'Dropping constraints from [dbo].[b_klient]'
GO
ALTER TABLE [dbo].[b_klient] DROP CONSTRAINT [DF__b_klient__C_LET__7B264821]
GO
PRINT N'Dropping constraints from [dbo].[b_klient]'
GO
ALTER TABLE [dbo].[b_klient] DROP CONSTRAINT [DF__b_klient__GOD_R__76619304]
GO
PRINT N'Dropping constraints from [dbo].[b_klient]'
GO
ALTER TABLE [dbo].[b_klient] DROP CONSTRAINT [DF__b_klient__K_OBR__7755B73D]
GO
PRINT N'Dropping constraints from [dbo].[b_klient]'
GO
ALTER TABLE [dbo].[b_klient] DROP CONSTRAINT [DF__b_klient__K_SPEC__7849DB76]
GO
PRINT N'Dropping constraints from [dbo].[b_klient]'
GO
ALTER TABLE [dbo].[b_klient] DROP CONSTRAINT [DF__b_klient__K_SP__793DFFAF]
GO
PRINT N'Dropping constraints from [dbo].[b_klient]'
GO
ALTER TABLE [dbo].[b_klient] DROP CONSTRAINT [DF__b_klient__K_SOCS__7A3223E8]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_UKS__7FEAFD3E]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__NPP_KPO__00DF2177]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_OBRASCH__01D345B0]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__F_KRIZIS__03BB8E22]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_INF_CEN__0697FACD]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__GOD_R__09746778]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_ADR__0A688BB1]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_SPEC__0D44F85C]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_HELP_OR__0F2D40CE]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_SP__10216507]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_UP__1209AD79]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_OBR__12FDD1B2]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_SOCSTAT__13F1F5EB]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_RAB__14E61A24]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_PRICH__15DA3E5D]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_POM__16CE6296]
GO
PRINT N'Dropping constraints from [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] DROP CONSTRAINT [DF__b_kpo__K_KONS__17C286CF]
GO
PRINT N'Dropping index [IX_b_kpo_NPP_KPO] from [dbo].[b_kpo]'
GO
DROP INDEX [IX_b_kpo_NPP_KPO] ON [dbo].[b_kpo]
GO
PRINT N'Dropping index [IX_b_kpo_K_SPEC] from [dbo].[b_kpo]'
GO
DROP INDEX [IX_b_kpo_K_SPEC] ON [dbo].[b_kpo]
GO
PRINT N'Dropping index [IX_b_kpo_K_RAB] from [dbo].[b_kpo]'
GO
DROP INDEX [IX_b_kpo_K_RAB] ON [dbo].[b_kpo]
GO
PRINT N'Creating [dbo].[b_kpo]'
GO
CREATE TABLE [dbo].[b_kpo]
(
[K_KPO] [int] NOT NULL IDENTITY(1, 1),
[K_UKS] [int] NULL,
[NPP_KPO] [int] NULL,
[K_OBRASCH] [int] NULL,
[F_DEN] [bit] NOT NULL,
[F_KRIZIS] [bit] NOT NULL,
[D_OBRASCH] [datetime] NULL,
[V_OBRASCH] [datetime] NULL,
[K_INF_CENTR] [int] NULL,
[F_KLIENT] [bit] NOT NULL,
[GOD_R] [int] NULL,
[K_ADR] [int] NULL,
[N_PRICH] [ntext] COLLATE Cyrillic_General_CI_AS NULL,
[F_KONS] [bit] NOT NULL,
[K_SPEC] [int] NULL,
[D_KONS] [datetime] NULL,
[V_KONS] [datetime] NULL,
[K_HELP_ORG] [int] NULL,
[N_HELP] [nvarchar] (150) COLLATE Cyrillic_General_CI_AS NULL,
[K_SP] [int] NULL,
[F_DETI] [bit] NOT NULL,
[K_UP] [int] NULL,
[K_OBR] [int] NULL,
[K_SOCSTAT] [int] NULL,
[K_RAB] [int] NULL,
[K_PRICH] [int] NULL,
[K_POM] [int] NULL,
[N_PRIM] [ntext] COLLATE Cyrillic_General_CI_AS NULL,
[K_KONS] [int] NULL,
[N_FA] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[N_IM] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[N_OT] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[K_POL] [tinyint] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'Creating index [IX_b_kpo_NPP_KPO] on [dbo].[b_kpo]'
GO
CREATE NONCLUSTERED INDEX [IX_b_kpo_NPP_KPO] ON [dbo].[b_kpo] ([NPP_KPO]) ON [PRIMARY]
GO
PRINT N'Creating index [IX_b_kpo_K_SPEC] on [dbo].[b_kpo]'
GO
CREATE NONCLUSTERED INDEX [IX_b_kpo_K_SPEC] ON [dbo].[b_kpo] ([K_SPEC]) ON [PRIMARY]
GO
PRINT N'Creating index [IX_b_kpo_K_RAB] on [dbo].[b_kpo]'
GO
CREATE NONCLUSTERED INDEX [IX_b_kpo_K_RAB] ON [dbo].[b_kpo] ([K_RAB]) ON [PRIMARY]
GO
PRINT N'Creating primary key [aaaaab_kpo_PK] on [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] ADD CONSTRAINT [aaaaab_kpo_PK] PRIMARY KEY NONCLUSTERED  ([K_KPO]) ON [PRIMARY]
GO
PRINT N'Creating index [IX_b_kpo_K_UKS] on [dbo].[b_kpo]'
GO
CREATE NONCLUSTERED INDEX [IX_b_kpo_K_UKS] ON [dbo].[b_kpo] ([K_KPO]) ON [PRIMARY]
GO
PRINT N'Creating index [IX_b_kpo_D_OBRASCH] on [dbo].[b_kpo]'
GO
CREATE NONCLUSTERED INDEX [IX_b_kpo_D_OBRASCH] ON [dbo].[b_kpo] ([D_OBRASCH]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[b_klient]'
GO
CREATE TABLE [dbo].[b_klient]
(
[K_KLIENT] [int] NOT NULL IDENTITY(1, 1),
[K_UKS] [int] NULL,
[F_INITSIATOR] [bit] NOT NULL,
[K_RODSTV_OTN] [int] NULL,
[GOD_R] [int] NULL,
[K_OBR] [int] NULL,
[K_SPEC] [int] NULL,
[K_SP] [int] NULL,
[K_SOCSTAT] [int] NULL,
[N_FA] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[N_IM] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[N_OT] [varchar] (50) COLLATE Cyrillic_General_CI_AS NULL,
[K_POL] [tinyint] NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [aaaaab_klient_PK] on [dbo].[b_klient]'
GO
ALTER TABLE [dbo].[b_klient] ADD CONSTRAINT [aaaaab_klient_PK] PRIMARY KEY NONCLUSTERED  ([K_KLIENT]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[vUks]'
GO
CREATE VIEW dbo.vUks
AS
SELECT     K_UKS, NPP_UKS, D_DZ, K_ZAN, K_ADR, K_RAB, K_INF_CENTR, C_PO, N_ORG_NAPRAVIL, K_SP, C_GOD_SP, F_DETI_NET, K_PRICH_UR, K_PRICH, 
                      LTRIM(STR(NPP_UKS)) + ' - ' + LTRIM(STR(YEAR(D_DZ))) AS NPP_UKSyear
FROM         dbo.b_uks
GO
PRINT N'Adding constraints to [dbo].[b_klient]'
GO
ALTER TABLE [dbo].[b_klient] ADD CONSTRAINT [DF_b_klient_K_POL] DEFAULT ((2)) FOR [K_POL]
GO
ALTER TABLE [dbo].[b_klient] ADD CONSTRAINT [DF__b_klient__K_UKS__719CDDE7] DEFAULT ((0)) FOR [K_UKS]
GO
ALTER TABLE [dbo].[b_klient] ADD CONSTRAINT [DF__b_klient__F_INIT__73852659] DEFAULT ((0)) FOR [F_INITSIATOR]
GO
ALTER TABLE [dbo].[b_klient] ADD CONSTRAINT [DF__b_klient__K_RODS__74794A92] DEFAULT ((0)) FOR [K_RODSTV_OTN]
GO
PRINT N'Adding constraints to [dbo].[b_kpo]'
GO
ALTER TABLE [dbo].[b_kpo] ADD CONSTRAINT [DF__b_kpo__F_KRIZIS__03BB8E22] DEFAULT ((1)) FOR [F_KRIZIS]
GO
ALTER TABLE [dbo].[b_kpo] ADD CONSTRAINT [DF_b_kpo_K_POL] DEFAULT ((2)) FOR [K_POL]
GO
ALTER TABLE [dbo].[b_kpo] ADD CONSTRAINT [DF__b_kpo__F_DEN__02C769E9] DEFAULT ((-1)) FOR [F_DEN]
GO
ALTER TABLE [dbo].[b_kpo] ADD CONSTRAINT [DF__b_kpo__D_OBRASCH__04AFB25B] DEFAULT (CONVERT([datetime],CONVERT([varchar],getdate(),(1)),(1))) FOR [D_OBRASCH]
GO
ALTER TABLE [dbo].[b_kpo] ADD CONSTRAINT [DF__b_kpo__F_KLIENT__078C1F06] DEFAULT ((1)) FOR [F_KLIENT]
GO
ALTER TABLE [dbo].[b_kpo] ADD CONSTRAINT [DF__b_kpo__F_KONS__0B5CAFEA] DEFAULT ((0)) FOR [F_KONS]
GO
ALTER TABLE [dbo].[b_kpo] ADD CONSTRAINT [DF__b_kpo__F_DETI__11158940] DEFAULT ((0)) FOR [F_DETI]
GO
PRINT N'Creating trigger [dbo].[TrigerInsertKpo] on [dbo].[b_kpo]'
GO
create TRIGGER TrigerInsertKpo 
ON dbo.b_kpo 
FOR INSERT
AS
-- Тригер увеличивающий автоматически NPP_KPO при встакве 
begin
	DECLARE @NPP_KPO int
	DECLARE @K_KPO int
	
	set @NPP_KPO = (Select NPP_KPO from inserted)
	set @K_KPO = (Select K_KPO from inserted)
	
	if @NPP_KPO = null or @NPP_KPO = 0
	BEGIN
		set @NPP_KPO  = (select max(NPP_KPO) + 1 from b_kpo where  YEAR(D_OBRASCH) = YEAR(GETDATE()))
		UPDATE b_kpo SET NPP_KPO = @NPP_KPO where b_kpo.K_KPO = @K_KPO
	END
end
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'DefaultValue', N'null', 'SCHEMA', N'dbo', 'TABLE', N'b_kpo', 'COLUMN', N'GOD_R'
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "b_kpo"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', N'dbo', 'VIEW', N'vKpo', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', 1, 'SCHEMA', N'dbo', 'VIEW', N'vKpo', NULL, NULL
GO
